#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   db=zx_dws
else
   db=$1
fi

${HIVE_HOME} -S -e "CREATE DATABASE IF NOT EXISTS ${db};
--客户意向小时统计宽表
DROP TABLE IF EXISTS ${db}.dws_relationship_hourcount;
CREATE TABLE ${db}.dws_relationship_hourcount
(

--维度
    date_time           STRING,
    online_offline      STRING COMMENT '线上线下,origin_type,NETSERVICE or PRESIGNUP 表示线上，其他的值表示线下',
    new_or_old          STRING COMMENT '新老学员',

    origin_type         STRING COMMENT '来源渠道',
    stu_area            STRING COMMENT '地区',
    itcast_subject_id   STRING COMMENT '学科Id',
    itcast_subject_name STRING COMMENT '学科名称',
    itcast_school_id    STRING COMMENT '校区Id',
    itcast_school_name  STRING COMMENT '校区名称',
    consult_centre_id   STRING COMMENT '咨询中心Id',
    consult_centre      STRING COMMENT '咨询中心',
--指标
    group_type          STRING COMMENT '分组类型',
    intended_count      INT COMMENT '意向用户数量'

) COMMENT '客户意向小时统计宽表'
    PARTITIONED BY (dt STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');

--客户意向小时统计宽表
DROP TABLE IF EXISTS ${db}.dws_clue_hourcount;
CREATE TABLE ${db}.dws_clue_hourcount
(

--维度
    date_time           STRING,
    online_offline     STRING COMMENT '线上线下',
    new_or_old         STRING COMMENT '新老学员',
--指标
    clue_count         INT COMMENT '线索数量',
    valid_clue_count   INT COMMENT '有效线索数量',
    percent_conversion DECIMAL(5, 2) COMMENT '有效线索转化率'

) COMMENT '客户线索小时统计宽表'
    PARTITIONED BY (dt STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');



"